﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;

/*
 * Copyright (c) 2009 - Jim Lehmer
 * 
 * Permission is hereby granted, free of charge, to any person
 * obtaining a copy of this software and associated documentation
 * files (the "Software"), to deal in the Software without
 * restriction, including without limitation the rights to use,
 * copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the
 * Software is furnished to do so, subject to the following
 * conditions:
 * 
 * The above copyright notice and this permission notice shall be
 * included in all copies or substantial portions of the Software.
 * 
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
 * OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
 * HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
 * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 * OTHER DEALINGS IN THE SOFTWARE.
 * 
 * You may contact the author at jim [dot] lehmer [at] gmail [dot] com.
 * 
 * This Web part is used to display a dynamic list of the most popular documents and pages in a
 * SharePoint site. It reads data from a table in a database that is expected to be updated
 * daily using the Microsoft LogParser tool going against the IIS logs. It is also expected
 * that all the columns in IIS logging are turned on. We use a scheduled task to scrape the
 * latest IIS logs once a day right after midnight, and then a scheduled SQL Server job to
 * trim the resulting table to the last 60 days of data so it doesn't get too big.
 * 
 * The idea is that the a stored proc is called by this Web part and brings back a set of
 * results showing the most popular "real" links (not just icons and stuff) for the past
 * "n" days (currently n=15). Then each of those URLs are searched individually in SharePoint.
 * Why do the search? Two reasons - the first is we don't want to display the URL to the
 * user, but instead the friendly (we hope) SharePoint title. Secondly, we do a search to
 * let SharePoint do security trimming so we don't show the user links they don't have
 * access to.
 * 
 * There is the SharePoint admin screen that shows most popular links, but (a) it's an
 * admin screen, and (b) it shows the URLs, not a user-friendly SharePoint title. Whereas
 * this Web part is meant to live on the front page of an intranet site, for example,
 * and allow people to get to the most popular docs on the site in a single click, with
 * "most popular" changing and remaining accurate over time.
 * 
 * Note: We search on each link individually. A version of this Web part was working where
 * the SharePoint query just had all the URLs OR'ed together and did one search, but that
 * then changed the sort order. By searching one at a time we preserve the order in terms
 * of link popularity, and for 10-20 links the whole thing is quick enough anyway.
 * 
 * Note: This has to be installed into the GAC since it does database access. But see TODO's
 * below.
 * 
 * TODO: Right now the GetTopHits stored proc contains a lot of the filtering logic for bringing
 * back candidates for this Web part to then search on in SharePoint. A lot of that should
 * probably be moved as browsable properties into this Web part and then GetTopHits should
 * be parameterized to use those values. OTOH, since one of the properties this Web part
 * does have is the ability to point to different stored procs, it is pretty easy to change
 * filtering for different sites in the stored proc, too. See the stored proc for more details.
 * 
 * TODO: Figure out how to have search results bring back docs that are attached to items in a list.
 * For example, right now this will find docs in doc libraries, but not docs that are attached
 * to a list item (like an announcement item with a Word document attached).
 * 
 * TODO: There is probably a better way to connect to the database in a "SharePointy" way
 * 
 */
namespace FCSF.SharePoint.PopularPages
{
    [Guid("4ebadfe1-fda2-4bb5-a29c-910ec473e258")]
    public class PopularPages : System.Web.UI.WebControls.WebParts.WebPart
    {
        LinksTable _links;

        [WebBrowsable(true),
        Personalizable(true),
        WebPartStorage(Storage.Shared),
        WebDisplayName("Log Database Connection String"),
        WebDescription("Connection string to the database holding the LogParser-parsed IIS logs from this SharePoint server."),
        SPWebCategoryName("Options")]
        public string LogDBConnectionString { get; set; }

        [WebBrowsable(true),
        Personalizable(true),
        WebPartStorage(Storage.Shared),
        WebDisplayName("Log Stored Procedure"),
        WebDescription("The name of the stored proc that brings back the most popular pages."),
        SPWebCategoryName("Options")]
        public string LogStoredProcName { get; set; }

        [WebBrowsable(true),
        Personalizable(true),
        WebPartStorage(Storage.Shared),
        WebDisplayName("Number of Links"),
        WebDescription("The number of links to display to the user."),
        SPWebCategoryName("Options")]
        public int NumberOfLinks { get; set; }

        public PopularPages()
        {
            if (NumberOfLinks <= 0)
            {
                NumberOfLinks = 15;
            }
        }

        protected override void CreateChildControls()
        {
            base.CreateChildControls();

            try
            {
                int linksDisplayed = 0;
                _links = (LinksTable)Page.LoadControl("/_controltemplates/LinksTable.ascx");
                Microsoft.Office.Server.Search.Query.QueryService searchService = new Microsoft.Office.Server.Search.Query.QueryService(); // MOSS 2007 SDK
                //Microsoft.SharePoint.Search.Query.QueryService searchService = new Microsoft.SharePoint.Search.Query.QueryService(); // WSS 3.0 SDK
                SPSite siteColl = SPControl.GetContextSite(Context);
                string query = "<?xml version='1.0' encoding='utf-8' ?>" +
                                "<QueryPacket xmlns='urn:Microsoft.Search.Query' Revision='1000'>" +
                                    "<Query domain='QDomain'>" +
                                        "<SupportedFormats>" +
                                            "<Format>urn:Microsoft.Search.Response.Document.Document</Format>" +
                                        "</SupportedFormats>" +
                                        "<Context>" +
                                            "<QueryText language='en-US' type='MSSQLFT'>" +
                                                "<![CDATA[ SELECT Filename, Path FROM portal..scope() WHERE Path = '{0}' ORDER BY \"Rank\" DESC -- ]]>" +
                                            "</QueryText>" +
                                        "</Context>" +
                                        "<Range>" +
                                            "<StartAt>1</StartAt>" +
                                            "<Count>{1}</Count>" +
                                        "</Range>" +
                                        "<EnableStemming>false</EnableStemming>" +
                                        "<TrimDuplicates>true</TrimDuplicates>" +
                                        "<IgnoreAllNoiseQuery>true</IgnoreAllNoiseQuery>" +
                                        "<ImplicitAndBehavior>true</ImplicitAndBehavior>" +
                                    "</Query>" +
                                "</QueryPacket>";
                string searchTerm = "";
                string url = "";
                _links.ID = "LinksListCtrl";

                using (SqlConnection conn = new SqlConnection(LogDBConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand(LogStoredProcName, conn) { CommandType = CommandType.StoredProcedure })
                    {
                        conn.Open();
                        SqlDataReader results = cmd.ExecuteReader();

                        while (results.Read())
                        {
                            // IIS logs put "+" signs in the URL for spaces - we need to get them back to spaces
                            // for SharePoint search to find them.
                            url = results["csUriStem"].ToString().Replace("+", " ");
                            // HACK: Right now we are assuming the default Web site in IIS (port 80).
                            //
                            // TODO: Have the stored proc bring back the sPort column as well
                            // and use that to construct a URL for non-port-80 Web applications.
                            // There is probably a better way to just get the entire base URL
                            // via the SharePoint APIs, but this works for now.
                            searchTerm = "http://" + siteColl.HostName + url;

                            if (linksDisplayed < NumberOfLinks)
                            {
                                DataSet searchResult = searchService.QueryEx(String.Format(query, searchTerm, NumberOfLinks));

                                if (searchResult.Tables.Count > 0 && searchResult.Tables[0].Rows.Count > 0)
                                {
                                    for (int i = 0; i < searchResult.Tables[0].Rows.Count; i++)
                                    {
                                        HyperLink link = new HyperLink() { Text = searchResult.Tables[0].Rows[i].ItemArray[0].ToString(), NavigateUrl = searchResult.Tables[0].Rows[i].ItemArray[1].ToString() };
                                        _links.Controls.Add(link);
                                        Literal br = new Literal() { Text = "<br />" };
                                        _links.Controls.Add(br);
                                        linksDisplayed++;
                                    }
                                }
                            }
                            else
                            {
                                // We've retrieved more than the limit, so we're done.
                                break;
                            }
                        }

                        results.Close();
                        conn.Close();
                        Controls.Add(_links);
                    }
                }
            }
            catch (Exception ex)
            {
                Label errMessage = new Label();
                errMessage.Text = String.Format("A problem occurred while trying to load the most frequently used pages. {0}", ex.Message);
                Controls.Clear();
                Controls.Add(errMessage);
            }
            finally
            {
            }
        }
    }
}